package com.xzcedu.jdbc.batch_;

import com.xzcedu.jdbc.utils.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author 徐州城
 * @version 1.0
 * 演示java的批处理
 */
public class Batch_ {

    //传统方法，添加5000条数据到admin2
    @Test
    public void noBatch() throws SQLException {

        //得到连接
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.println("开始执行");
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack" + (i + 1));
            preparedStatement.setString(2, "666");
            preparedStatement.executeUpdate();
        }

        long end = System.currentTimeMillis();
        System.out.println("耗时=" + (end - start)); //耗时=6061

        //关闭连接
        JDBCUtils.close(null, preparedStatement, connection);
    }

    //使用批量处理方式
    @Test
    public void batch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.println("开始执行");
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack" + (i + 1));
            preparedStatement.setString(2, "666");
            //将sql 语句加入批处理包中 -> 看源码
            /*
                1. 第一次就创建一个 ArrayList - elementDate => Object[]
                2. elementDate => Object[] 就会存放预处理的sql语句
                3. 当elementDate满后就按照1.5倍扩容
                4. 当添加到指定的值后，就executeBatch();
                5. 批量处理会减少发送sql语句的网络开销，而且减少编译的次数，因此效率提高
                 public void addBatch() throws SQLException {
                    synchronized(this.checkClosed().getConnectionMutex()) {
                        if (this.batchedArgs == null) {
                            this.batchedArgs = new ArrayList();
                        }

                        for(int i = 0; i < this.parameterValues.length; ++i) {
                            this.checkAllParametersSet(this.parameterValues[i], this.parameterStreams[i], i);
                        }

                        this.batchedArgs.add(new PreparedStatement.BatchParams(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull));
                    }
                }
             */
            preparedStatement.addBatch();
            //当有一千条记录时，在批量执行
            if ((i + 1) % 1000 == 0) {//满1000条 sql
                preparedStatement.executeBatch();
                //清空 批处理包中数据
                preparedStatement.clearBatch();
            }
        }

        long end = System.currentTimeMillis();
        System.out.println("批量方式 耗时=" + (end - start)); //批量方式 耗时=205

        //关闭连接
        JDBCUtils.close(null, preparedStatement, connection);
    }


}
